﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebSite.App_Code;
using WebSite.App_Data;
using WebSite.Models;
using System.Resources;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Web.Configuration;

namespace WebSite.Controllers
{
    [HandleError]
    public class BudgetController : BaseController
    {
        #region Action.MasterData

        public ActionResult MasterData()
        {
            Session.Remove("Budget.MasterData.Review");

            BudgetMasterDataModel ModelData = new BudgetMasterDataModel();
            
            return View("MasterData", ModelData);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult MasterData_Upload(FormCollection collection)
        {
            Session.Remove("Budget.MasterData.Review");

            if (Request.Files.Count == 0)
            {
                ViewData["DB.Operation.Result"] = "No Data";
                return MasterData();
            }

            var c = Request.Files[0];
            if (c == null || c.ContentLength == 0)
            {
                ViewData["DB.Operation.Result"] = "No Data";
                return MasterData();
            }

            string fileName = Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            fileName = Server.MapPath(WebConfigurationManager.AppSettings["UploadTmpPath"]) + fileName;
            c.SaveAs(fileName);

            Excel._Application app = null;
            Excel.Workbook book = null;
            Excel.Worksheet ws = null;

            try
            {
                app = new Excel.ApplicationClass();

                book = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                ws = (Excel.Worksheet)book.Worksheets.get_Item(1);

                Dictionary<String, String> lsPC = new Dictionary<String, String>();
                Dictionary<String, String> lsPT = new Dictionary<String, String>();
                Dictionary<String, String> lsBS = new Dictionary<String, String>();
                Dictionary<String, String> lsYN = new Dictionary<String, String>();
                Dictionary<String, String> lsD = new Dictionary<String, String>();
                Dictionary<String, String> lsAP = new Dictionary<String, String>();
                Dictionary<String, String> lsU = new Dictionary<String, String>();
                Dictionary<String, String> lsY = new Dictionary<String, String>();

                ExcelSheetItem.GetList(ref lsPC, ref lsPT, ref lsBS, ref lsYN, ref lsD, ref lsAP, ref lsU, ref lsY);

                List<Dictionary<String, ExcelSheetCellItem>> Table = new List<Dictionary<String, ExcelSheetCellItem>>();

                for (int i = 3; i <= ws.UsedRange.Rows.Count; i++)
                {
                    if (app.WorksheetFunction.CountA(ws.UsedRange.get_Range("A" + i.ToString() + ":AH" + i.ToString())) == 0)
                        continue;

                    Dictionary<String, ExcelSheetCellItem> Row = new Dictionary<String, ExcelSheetCellItem>();

                    Row.Add("类别", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("A" + i.ToString()).Value2), InList = lsPC });
                    Row.Add("年度", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("B" + i.ToString()).Value2), InList = lsY });
                    Row.Add("类型", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("C" + i.ToString()).Value2), InList = lsPT });
                    if (Row["类别"].InList.Keys.Contains(Row["类别"].Text))
                    {
                        if (Row["类别"].InList[Row["类别"].Text] == "4")
                            Row["类型"].IsRequired = true;
                    }
                    Row.Add("编制单位", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("D" + i.ToString()).Value2), InList = lsD, IsRequired = true });
                    Row.Add("项目名称", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("E" + i.ToString()).Value2), MaxLength = 50 });
                    Row.Add("项目代号", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("F" + i.ToString()).Value2), MaxLength = 10 });
                    Row.Add("年度进度", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("G" + i.ToString()).Value2), InList = lsAP });
                    Row.Add("经费来源", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("H" + i.ToString()).Value2), MaxLength = 20, IsRequired = true });
                    Row.Add("项目周期-起", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("I" + i.ToString()).Text), IsDate = true, IsRequired = true });
                    Row.Add("项目周期-止", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("J" + i.ToString()).Text), IsDate = true, IsRequired = true });
                    Row.Add("项目负责人", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("K" + i.ToString()).Value2), InList = lsU });
                    Row.Add("是否为软课费", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("L" + i.ToString()).Value2), InList = lsYN });
                    Row.Add("状态", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("M" + i.ToString()).Value2), InList = lsBS });
                    Row.Add("是否垫支", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("N" + i.ToString()).Value2), InList = lsYN });

                    if (lsYN.Keys.Contains(Row["是否垫支"].Text) && lsYN[Row["是否垫支"].Text].ToUpper() == "TRUE")
                    {
                        Row.Add("拟到经费总额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("O" + i.ToString()).Value2), IsDecimal = true });
                        Row.Add("预计经费到位时间", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("P" + i.ToString()).Text), IsDate = true });
                        Row.Add("垫支金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("Q" + i.ToString()).Value2), IsDecimal = true });
                        Row.Add("预计归垫时间", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("R" + i.ToString()).Text), IsDate = true });
                        Row.Add("归垫责任人", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("S" + i.ToString()).Value2), InList = lsU });
                    }
                    else
                    {
                        Row.Add("拟到经费总额", new ExcelSheetCellItem() { Text = "0" });
                        Row.Add("预计经费到位时间", new ExcelSheetCellItem() { Text = String.Empty });
                        Row.Add("垫支金额", new ExcelSheetCellItem() { Text = "0" });
                        Row.Add("预计归垫时间", new ExcelSheetCellItem() { Text = String.Empty });
                        Row.Add("归垫责任人", new ExcelSheetCellItem() { Text = String.Empty });
                    }

                    Row.Add("垫支状态", new ExcelSheetCellItem() { Text = String.Empty });
                    Row.Add("归垫后余额", new ExcelSheetCellItem() { Text = "0" });
                    Row.Add("尚未归垫", new ExcelSheetCellItem() { Text = "0" });

                    Row.Add("预算金额", new ExcelSheetCellItem() { Text = "0" });
                    Row.Add("标准领报", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("U" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("项目经费", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("V" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("家底弥补", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("W" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("所家底", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("X" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("科技处家底", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("Y" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("政治部家底", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("Z" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("管理处家底", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AA" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("其他收入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AB" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("上年转入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AC" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("备注(预算金额)", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AD" + i.ToString()).Value2), MaxLength = 50 });
                    Row.Add("历年支出金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AE" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("备注(历年支出)", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AF" + i.ToString()).Value2), MaxLength = 50 });
                    Row.Add("当年支出金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AG" + i.ToString()).Value2), IsDecimal = true });
                    Row.Add("备注(当年支出)", new ExcelSheetCellItem() { Text = Convert.ToString(ws.UsedRange.get_Range("AH" + i.ToString()).Value2), MaxLength = 50 });
                    Row.Add("检查结果", new ExcelSheetCellItem());
                    Row.Add("处理方案", new ExcelSheetCellItem());

                    Row["项目代号"].IsRequired = true;

                    if (app.WorksheetFunction.CountIf(ws.get_Range("L" + i.ToString() + ":AH" + i.ToString()), "<>") +
                        app.WorksheetFunction.CountIf(ws.get_Range("B" + i.ToString()), "<>") +
                        app.WorksheetFunction.CountIf(ws.get_Range("G" + i.ToString()), "<>") == 0)
                    {
                        if (!BudgetMasterDataModel.CheckProjectExists(Row["项目代号"].Text))
                        {
                            Row["处理方案"] = new ExcelSheetCellItem()
                            {
                                Text = "新建项目",
                                Value = "ADD PROJECT",
                            };
                        }
                        else
                        {
                            Row["处理方案"] = new ExcelSheetCellItem()
                            {
                                Text = "调整项目",
                                Value = "ADJUST PROJECT",
                            };
                        }
                    }
                    else
                    {
                        Row["年度"].IsRequired = true;

                        if (!BudgetMasterDataModel.CheckProjectExists(Row["项目代号"].Text))
                        {
                            Row["类别"].IsRequired = true;

                            if (Row["类别"].InList.ContainsKey(Row["类别"].Text))
                            {
                                if (Row["类别"].InList[Row["类别"].Text] == "4")
                                    Row["类型"].IsRequired = true;
                            }
                            Row["编制单位"].IsRequired = true;
                            Row["项目负责人"].IsRequired = true;

                            Row["年度进度"].IsRequired = true;
                            Row["是否为软课费"].IsRequired = true;
                            //Row["状态"].IsRequired = true;
                            Row["是否垫支"].IsRequired = true;

                            Row["处理方案"] = new ExcelSheetCellItem()
                            {
                                Text = "新建项目，预算",
                                Value = "ADD PROJECT + ADD BUDGET",
                            };
                        }
                        else if (!BudgetMasterDataModel.CheckBudgetExists(Row["年度"].Text, Row["项目代号"].Text))
                        {
                            Row["年度进度"].IsRequired = true;
                            Row["是否为软课费"].IsRequired = true;
                            //Row["状态"].IsRequired = true;
                            Row["是否垫支"].IsRequired = true;
                            Row["处理方案"] = new ExcelSheetCellItem()
                            {
                                Text = "新建预算",
                                Value = "ADJUST PROJECT + ADD BUDGET",
                            };
                        }
                        else if (BudgetMasterDataModel.CheckBudgetCanAdjust(Row["年度"].Text, Row["项目代号"].Text))
                        {
                            Row["处理方案"] = new ExcelSheetCellItem()
                            {
                                Text = "调整预算",
                                Value = "ADJUST PROJECT + ADJUST BUDGET",
                            };
                        }
                        else
                        {
                            Row["处理方案"] = new ExcelSheetCellItem()
                            {
                                Text = "预算不能调整",
                            };
                        }
                    }

                    Table.Add(Row);
                }

                if (Table.Count == 0)
                {
                    ViewData["DB.Operation.Result"] = "No Data";
                }
                else
                {
                    Boolean blR = ExcelSheetItem.Check(Table);

                    #region 补充操作：金额计算

                    foreach (Dictionary<String, ExcelSheetCellItem> oRow in Table)
                    {
                        BudgetCalculate C = new BudgetCalculate();

                        if (!oRow.Where(o => o.Value.IsError).Any())
                        {
                            C.Calculate(oRow["项目代号"].Text
                                , oRow["年度"].Text
                                , oRow["类别"].Text
                                , oRow["垫支金额"].Text
                                , "0"
                                , oRow["标准领报"].Text
                                , oRow["项目经费"].Text
                                , oRow["家底弥补"].Text
                                , oRow["其他收入"].Text
                                , oRow["上年转入"].Text
                                , oRow["所家底"].Text
                                , oRow["政治部家底"].Text
                                , oRow["科技处家底"].Text
                                , oRow["管理处家底"].Text
                                , null);

                            oRow["垫支状态"].Text = C.AS.Status;
                            oRow["归垫后余额"].Text = C.f.ToString();
                            oRow["是否垫支"].Text = C.IsA.Name;
                            oRow["预算金额"].Text = C.S.ToString();
                            oRow["尚未归垫"].Text = C.z.ToString();
                        }
                    }

                    #endregion

                    ViewData["DB.Upload.Result"] = blR;

                    Session["Budget.MasterData.Review"] = Table;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (book != null)
                    book.Close(false, Type.Missing, Type.Missing);

                if (app != null)
                    app.Quit();

                ws = null;
                book = null;
                app = null;

                GC.Collect();

                System.IO.File.Delete(fileName);
            }

            return View("MasterData", new BudgetMasterDataModel());
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult MasterData_Submit()
        {
            List<Dictionary<String, ExcelSheetCellItem>> Table = (List<Dictionary<String, ExcelSheetCellItem>>)Session["Budget.MasterData.Review"];

            foreach (Dictionary<String, ExcelSheetCellItem> Row in Table)
            {
                if (Row.Count(o => o.Value.IsError) > 0)
                    continue;

                String Result = BudgetMasterDataModel.AddMasterData(Row);

                Row["处理方案"].Text += Result;
            }

            ViewData["Budget.MasterData.Review"] = Table;

            return MasterData();
        }

        public ActionResult MasterData_Delete(String ID)
        {
            ProjectModel M = new ProjectModel();
            M.U = this.CurrentUser;

            M.DeleteItem(ID);

            return MasterData();
        }

        #endregion

        #region Action.NormalBudgetUpload

        public ActionResult NormalBudgetUpload()
        {
            Session.Remove("Budget.Data.Review");

            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            M.Init();

            ViewData["Budget.NormalBudgetUpload.BudgetList"] = M.GetCurrentBudget(new List<string>());

            return View("NormalBudgetUpload", M);
        }

        public ActionResult NormalBudgetUpload_DownloadTemplate(FormCollection collection, int id)
        {
            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            List<BudgetItem> lsAll = M.GetCurrentBudget(collection["BudgetList"], ',');

            if (lsAll.Count == 0)
            {
                ViewData["DB.Operation.Result"] = "请选择预算";
                return NormalBudgetUpload();
            }

            return DownloadTemplate(id, lsAll, false);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult NormalBudgetUpload_Upload(FormCollection collection)
        {
            if (Request.Files.Count == 0)
            {
                ViewData["DB.Operation.Result"] = Resources.Message.Alter_FileNoExists;
                return NormalBudgetUpload();
            }

            var c = Request.Files[0];
            if (c == null || c.ContentLength == 0)
            {
                ViewData["DB.Operation.Result"] = Resources.Message.Alter_FileNoExists;
                return NormalBudgetUpload();
            }

            string fileName = Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            fileName = Server.MapPath(WebConfigurationManager.AppSettings["UploadTmpPath"]) + fileName;
            c.SaveAs(fileName);

            List<TemplateSheetItem> lsSheet = new List<TemplateSheetItem>();
            String ErrMsg = String.Empty;

            if (!Budget_Upload_Check(fileName, "1", ref lsSheet, L_ActionType.Upload, ref ErrMsg))

                ViewData["DB.Operation.Result"] = ErrMsg;

            Session["Budget.Data.Review"] = lsSheet;

            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            M.Init();

            ViewData["Budget.NormalBudgetUpload.BudgetList"] = M.GetCurrentBudget();

            return View("NormalBudgetUpload", M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult NormalBudgetUpload_Submit()
        {
            List<TemplateSheetItem> Table = (List<TemplateSheetItem>)Session["Budget.Data.Review"];

            foreach (TemplateSheetItem Row in Table)
            {
                NormalBudgetUploadModel M = new NormalBudgetUploadModel();
                M.U = this.CurrentUser;

                String Result = M.AddData(Row);

                Row.BugetInfo["处理方案"].Text += Result;
            }

            ViewData["Budget.Data.Review"] = Table;

            return NormalBudgetUpload();
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult NormalBudgetUpload_Delete(String id)
        {
            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            ViewData["DB.Operation.Result"] = M.DeleteData(id);

            return NormalBudgetUpload();
        }

        #endregion

        #region Action.QueryBudget

        public ActionResult QueryBudget()
        {
            QueryBudgetModel M = new QueryBudgetModel();
            M.U = this.CurrentUser;

            M.Init();

            return View("QueryBudget", M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult QueryBudget(QueryBudgetModel ModelData)
        {
            //Dictionary<string, string> Where = new Dictionary<string, string>();
            //Where["Year"] = ModelData.Where_Year;
            //Where["BudgetStatus"] = ModelData.Where_BudgetStatus;
            //Where["ProjectCategoryID"] = ModelData.Where_ProjectCatgory;
            //Where["ProjectTypeID"] = ModelData.Where_ProjectType;
            //Where["DepartmentName"] = ModelData.Where_Department;
            //Where["ProjectPICName"] = ModelData.Where_ProjectPIC;
            //Where["ProjectName"] = ModelData.Where_ProjectName;
            //Where["ProjectCode"] = ModelData.Where_ProjectCode;
            //Where["IsAdvence"] = ModelData.Where_IsAdvence;
            //Where["IsSoft"] = ModelData.Where_IsSoft;
            //Where["IsHis"] = ModelData.Where_IsHis.ToString();

            //Session["Budget.Query.Where"] = Where;

            ModelData.U = this.CurrentUser;

            ModelData.Query();

            ViewData["QueryResult"] = ModelData.QueryResult;

            ModelData.Init();

            return View("QueryBudget", ModelData);

        }

        #endregion

        #region Action.BudgetDetail

        public ActionResult BudgetDetail(String ID, Boolean His, Int32? Version, L_BudgetDetailPageType? Type)
        {
            BudgetDetailModel M = new BudgetDetailModel(this.CurrentUser);

            if (Type.HasValue)
                M.PageType = Type.Value;

            M.Init(ID, His, Version);

            if (!M.CheckRight(this.CurrentUser, His))
            {
                ViewData["DB.Operation.Result"] = "无权查看该预算信息!";
                return View("BudgetDetail",new BudgetDetailModel(this.CurrentUser));
            }
            else
                return View("BudgetDetail", M);
        }

        #endregion

        #region Action.DepartmentBudgetUpload

        public ActionResult DepartmentBudgetUpload()
        {
            Session.Remove("Budget.Data.Review");

            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            M.Init();

            ViewData["Budget.DepartmentBudgetUpload.BudgetList"] = M.GetCurrentBudget();

            return View("DepartmentBudgetUpload", M);
        }

        public ActionResult DepartmentBudgetUpload_DownloadEmptyTemplate(String id)
        {
            String strTempPath = Server.MapPath(WebConfigurationManager.AppSettings["DownloadTmpPath"]) + Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            System.IO.File.Copy(Server.MapPath(WebConfigurationManager.AppSettings["TemplatePath"] + "XLS--机关经费.xls"), strTempPath);

            String strType = String.Empty;
            String strYear = String.Empty;
            List<String> lsUser = new List<String>();
            String strUser = String.Empty;

            DBDataContext DB = new DBDataContext();
            DB.Connection.Open();

            IEnumerable<md_User> ieUserAll = from x in DB.md_Users where x.Status select x;
            IEnumerable<String> ieYear = from x in DB.list_Years where x.IsUsed select x.Name;
            IEnumerable<String> ieType = from x in DB.list_ProjcetTypes where x.ID == id.ToString() select x.Name;
            IEnumerable<String> ieUser = from x in DB.md_Users where x.DepartmentGuid == CurrentUser.Department.Department.Guid select x.UserName;

            DB.Connection.Close();

            if (ieYear.Any()) { strYear = ieYear.First(); }
            if (ieType.Any()) { strType = ieType.First(); }
            if (ieUser.Any()) { lsUser = ieUser.ToList(); }
            foreach (String aUser in lsUser) { strUser = strUser + aUser + ","; }
            if (strUser.Length > 0) { strUser = strUser.Substring(0, strUser.Length - 1); }

            Excel._Application app = null;
            Excel.Workbook book = null;
            Excel.Worksheet ws = null;

            try
            {
                app = new Excel.ApplicationClass();

                app.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
                app.DisplayAlerts = false;
                book = app.Workbooks.Open(strTempPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                book.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                Boolean isFirst = true;

                for (int i = 1; i <= book.Worksheets.Count; i++)
                {
                    ws = (Excel.Worksheet)book.Worksheets.get_Item(i);

                    if (ws.Name.ToLower() == "welcome")
                        continue;

                    if (ws.Name.ToLower() == "var")
                    {
                        ws.get_Range("C8").Value2 = id;
                        continue;
                    }

                    if (isFirst)
                        isFirst = false;
                    else
                        ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

                    ws.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                    ws.get_Range("B1").Value2 = strYear;
                    ws.get_Range("C1").Value2 = String.Format(ws.get_Range("C1").Text.ToString(), strType);
                    ws.get_Range("B2").Value2 = CurrentUser.Department.Department.DepartmentName;

                    ws.get_Range("I3:J3").Validation.Delete();
                    ws.get_Range("I3:J3").Validation.Add(Excel.XlDVType.xlValidateList
                        , Excel.XlDVAlertStyle.xlValidAlertStop
                        , Excel.XlFormatConditionOperator.xlBetween
                        , strUser);
                    ws.get_Range("I3:J3").Validation.IgnoreBlank = true;
                    ws.get_Range("I3:J3").Validation.InCellDropdown = true;
                    ws.get_Range("I3:J3").Validation.InputTitle = "";
                    ws.get_Range("I3:J3").Validation.ErrorTitle = "";
                    ws.get_Range("I3:J3").Validation.InputMessage = "";
                    ws.get_Range("I3:J3").Validation.ErrorMessage = "请选择正确的科目负责人";
                    ws.get_Range("I3:J3").Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                    ws.get_Range("I3:J3").Validation.ShowInput = true;
                    ws.get_Range("I3:J3").Validation.ShowError = true;

                    ws.get_Range("H5:I5").Validation.Delete();
                    ws.get_Range("H5:I5").Validation.Add(Excel.XlDVType.xlValidateList
                        , Excel.XlDVAlertStyle.xlValidAlertStop
                        , Excel.XlFormatConditionOperator.xlBetween
                        , strUser);
                    ws.get_Range("H5:I5").Validation.IgnoreBlank = true;
                    ws.get_Range("H5:I5").Validation.InCellDropdown = true;
                    ws.get_Range("H5:I5").Validation.InputTitle = "";
                    ws.get_Range("H5:I5").Validation.ErrorTitle = "";
                    ws.get_Range("H5:I5").Validation.InputMessage = "";
                    ws.get_Range("H5:I5").Validation.ErrorMessage = "请选择正确的负责人";
                    ws.get_Range("H5:I5").Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                    ws.get_Range("H5:I5").Validation.ShowInput = true;
                    ws.get_Range("H5:I5").Validation.ShowError = true;

                    ws.get_Range("A9").Formula = "=SUM(B9:I9)";

                    ws.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);
                }

                book.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                book.Save();
                book.Close(false, Type.Missing, Type.Missing);

                ws = null;
                book = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (book != null)
                    book.Close(false, Type.Missing, Type.Missing);

                if (app != null)
                    app.Quit();

                ws = null;
                book = null;
                app = null;

                GC.Collect();
            }

            List<Byte[]> lsByte = new List<byte[]>();

            FileStream fs = new FileStream(strTempPath, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            lsByte.Add(bytes);
            System.IO.File.Delete(strTempPath);

            Response.BufferOutput = false;

            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("XLS--机关经费--" + strType + ".xls"));
            foreach (Byte[] aByte in lsByte)
            {
                Response.BinaryWrite(aByte);
            }
            Response.Flush();
            Response.End();

            return new EmptyResult();
        }

        public ActionResult DepartmentBudgetUpload_DownloadTemplate(FormCollection collection, String id)
        {
            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            List<BudgetItem> lsAll = M.GetCurrentBudget(collection["BudgetList"], ',');

            if (lsAll.Count == 0)
            {
                ViewData["DB.Operation.Result"] = "请选择预算";
                return DepartmentBudgetUpload();
            }

            return DownloadTemplate(id, lsAll, false);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult DepartmentBudgetUpload_Upload(FormCollection collection)
        {
            if (Request.Files.Count == 0)
            {
                ViewData["DB.Operation.Result"] = Resources.Message.Alter_FileNoExists;
                return NormalBudgetUpload();
            }

            var c = Request.Files[0];
            if (c == null || c.ContentLength == 0)
            {
                ViewData["DB.Operation.Result"] = Resources.Message.Alter_FileNoExists;
                return NormalBudgetUpload();
            }

            string fileName = Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            fileName = Server.MapPath(WebConfigurationManager.AppSettings["UploadTmpPath"]) + fileName;
            c.SaveAs(fileName);

            List<TemplateSheetItem> lsSheet = new List<TemplateSheetItem>();
            String ErrMsg = String.Empty;

            if (!Budget_Upload_Check(fileName, "4", ref lsSheet, L_ActionType.Upload, ref ErrMsg))
                ViewData["DB.Operation.Result"] = ErrMsg;

            Session["Budget.Data.Review"] = lsSheet;

            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            M.Init();

            ViewData["Budget.DepartmentBudgetUpload.BudgetList"] = M.GetCurrentBudget();

            return View("DepartmentBudgetUpload", M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult DepartmentBudgetUpload_Submit()
        {
            List<TemplateSheetItem> Table = (List<TemplateSheetItem>)Session["Budget.Data.Review"];

            foreach (TemplateSheetItem Row in Table)
            {
                NormalBudgetUploadModel M = new NormalBudgetUploadModel();
                M.U = this.CurrentUser;

                String Result = M.AddData(Row);

                Row.BugetInfo["处理方案"].Text += Result;
            }

            ViewData["Budget.Data.Review"] = Table;

            return DepartmentBudgetUpload();
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult DepartmentBudgetUpload_Delete(String id)
        {
            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            ViewData["DB.Operation.Result"] = M.DeleteData(id);

            return DepartmentBudgetUpload();
        }

        #endregion

        #region Action.AppoverList

        public ActionResult AppoverList()
        {
            AppoverListModel M = new AppoverListModel();
            M.U = this.CurrentUser;

            M.Where_Year = this.CurrentYear;
            M.Init();
            M.Query();
            ViewData["QueryResult"] = M.QueryResult;

            return View(M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult AppoverList(AppoverListModel M, FormCollection collection)
        {
            M.U = this.CurrentUser;

            try
            {
                switch (M.PostingEvent)
                {
                    case L_PageEvent.B_AppoverList_Qury:
                        break;
                    case L_PageEvent.B_AppoverList_Export:
                        break;
                    case L_PageEvent.B_AppoverList_Import:
                        AppoverList_Upload(collection);
                        break;
                    case L_PageEvent.B_AppoverList_ImportConfirm:
                        AppoverList_UploadConfirm();
                        break;
                    case L_PageEvent.B_AppoverList_BatchReturn:
                        AppoverList_BatchReturn(M);
                        break;
                    case L_PageEvent.B_AppoverList_BatchAppove:
                        AppoverList_BatchAppove(M);
                        break;
                    //case L_PageEvent.Default:
                    //    return AppoverList();
                }
            }
            catch (Exception ex)
            {
                ViewData["DB.Operation.Result"] = ex.Message;
            }

            M.Init();
            M.Where_Year = this.CurrentYear;
            M.Query();
            ViewData["QueryResult"] = M.QueryResult;

            return View(M);
        }

        public void AppoverList_Upload(FormCollection collection)
        {
            if (Request.Files.Count == 0)
            {
                ViewData["DB.Operation.Result"] = Resources.Message.Alter_FileNoExists;
                return;
            }

            var c = Request.Files[0];
            if (c == null || c.ContentLength == 0)
            {
                ViewData["DB.Operation.Result"] = Resources.Message.Alter_FileNoExists;
                return;
            }

            string fileName = Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            fileName = Server.MapPath(WebConfigurationManager.AppSettings["UploadTmpPath"]) + fileName;
            c.SaveAs(fileName);

            List<TemplateSheetItem> lsSheet = new List<TemplateSheetItem>();
            String ErrMsg = String.Empty;

            if (!Budget_Upload_Check(fileName, null, ref lsSheet, L_ActionType.Approve, ref ErrMsg))
                ViewData["DB.Operation.Result"] = ErrMsg;

            Session["Budget.Data.Review"] = lsSheet;
        }

        public void AppoverList_UploadConfirm()
        {
            List<TemplateSheetItem> Table = (List<TemplateSheetItem>)Session["Budget.Data.Review"];

            foreach (TemplateSheetItem Row in Table)
            {
                NormalBudgetUploadModel M = new NormalBudgetUploadModel();
                M.U = this.CurrentUser;

                String Result = M.AddData(Row);

                Row.BugetInfo["处理方案"].Text += Result;
            }

            ViewData["Budget.Data.Review"] = Table;

            Session.Remove("Budget.Data.Review");
        }

        public void AppoverList_BatchReturn(AppoverListModel M)
        {
            String Rs = M.Rollback(this.CurrentYear);

            if (!String.IsNullOrEmpty(Rs.Trim()))
                ViewData["DB.Operation.Result"] = Rs;
        }

        public void AppoverList_BatchAppove(AppoverListModel M)
        {
            M.Approve(this.CurrentYear);
        }

        #endregion

        #region Action.AdjustList

        public ActionResult AdjustList()
        {
            AdjustListModel M = new AdjustListModel();
            M.U = this.CurrentUser;

            M.Where_Year = this.CurrentYear;
            M.Init();
            M.Query();
            ViewData["QueryResult"] = M.QueryResult;

            return View(M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult AdjustList(AdjustListModel M, FormCollection collection)
        {
            M.U = this.CurrentUser;

            try
            {
                switch (M.PostingEvent)
                {
                    case L_PageEvent.B_AdjustList_Qury:
                        break;
                    case L_PageEvent.B_AdjustList_Close:
                        AdjustList_Close(M);
                        break;
                    case L_PageEvent.B_AdjustList_CloseToAdjust:
                        AdjustList_CloseToAdjust(M);
                        break;
                    case L_PageEvent.B_AdjustList_CloseToComplete:
                        AdjustList_CloseToComplete(M);
                        break;
                    case L_PageEvent.B_AdjustList_CloseToOpen:
                        AdjustList_CloseToOpen(M);
                        break;
                }
            }
            catch (Exception ex)
            {
                ViewData["DB.Operation.Result"] = ex.Message;
            }

            M.Init();
            M.Where_Year = this.CurrentYear;
            M.Query();
            ViewData["QueryResult"] = M.QueryResult;

            return View(M);
        }

        public void AdjustList_Close(AdjustListModel M)
        {
            String Rs = M.Close();

            if (!String.IsNullOrEmpty(Rs.Trim()))
                ViewData["DB.Operation.Result"] = Rs;
        }

        public void AdjustList_CloseToAdjust(AdjustListModel M)
        {
            String Rs = M.CloseToAdjust();

            if (!String.IsNullOrEmpty(Rs.Trim()))
                ViewData["DB.Operation.Result"] = Rs;
        }

        public void AdjustList_CloseToComplete(AdjustListModel M)
        {
            String Rs = M.CloseToComplete();

            if (!String.IsNullOrEmpty(Rs.Trim()))
                ViewData["DB.Operation.Result"] = Rs;
        }

        public void AdjustList_CloseToOpen(AdjustListModel M)
        {
            String Rs = M.CloseToOpen();

            if (!String.IsNullOrEmpty(Rs.Trim()))
                ViewData["DB.Operation.Result"] = Rs;
        }

        #endregion

        #region Action.BudgetProgress

        public ActionResult BudgetProgress()
        {
            BudgetProgressModel M = new BudgetProgressModel();

            M.Init();

            M.Q_Year = this.CurrentYear;

            M.Query();

            return View(M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult BudgetProgress(BudgetProgressModel M)
        {
            M.Init();

            M.Query();

            return View(M);
        }

        #endregion

        #region Action.BudgetCombination

        public ActionResult BudgetCombination(int ID)
        {
            BudgetCombinationModel M = new BudgetCombinationModel();

            M.Init();

            foreach (SelectListItem i in M.DS_Status)
            {
                if (i.Value != "100")
                    M.Q_BStatus.Add(i.Value);
            }

            M.Q_Year = this.CurrentYear;

            M.Q_Category = ID.ToString();

            return View(M);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult BudgetCombination(BudgetCombinationModel M)
        {
            M.Init();

            DBDataContext DB = new DBDataContext();

            DB.Connection.Open();

            if (M.Q_Department != null) { M.Q_Department = M.Q_Department.Trim(); }
            if (M.Q_PIC != null) { M.Q_PIC = M.Q_PIC.Trim(); }
            if (M.Q_PjName != null) { M.Q_PjName = M.Q_PjName.Trim(); }
            if (M.Q_PjCode != null) { M.Q_PjCode = M.Q_PjCode.Trim(); }

            IEnumerable<vw_Budget> ieQuery = from b in DB.vw_Budgets
                                             where M.Q_BStatus.Contains(b.BudgetStatus)
                                             where b.IsHis.HasValue && !b.IsHis.Value
                                             where b.BudgetYear == M.Q_Year
                                             where b.ProjectCategoryID == M.Q_Category
                                             where (String.IsNullOrEmpty(M.Q_Department) || b.DepartmentName.Contains(M.Q_Department))
                                             where (String.IsNullOrEmpty(M.Q_PIC) || b.ProjectPICID.Contains(M.Q_PIC))
                                             where (String.IsNullOrEmpty(M.Q_PjName) || b.ProjectName.Contains(M.Q_PjName))
                                             where (String.IsNullOrEmpty(M.Q_PjCode) || b.ProjectCode.Contains(M.Q_PjCode))
                                             where (b.ProjectCategoryID != "4" ||
                                                (b.ProjectCategoryID == "4" && (String.IsNullOrEmpty(M.Q_Type) || b.ProjectTypeID == M.Q_Type)))
                                             //where b.BudgetStatus == Convert.ToString((int)L_BudgetStatus.Use) || b.BudgetStatus == Convert.ToString((int)L_BudgetStatus.Complete)
                                             group b by new { DN = b.DepartmentName, D = b.DepartmentID, PN = b.ProjectPICName, P = b.ProjectPICID, PJ = b.ProjectCode } into g
                                             select new vw_Budget()
                              {
                                  DepartmentName = g.Key.DN,
                                  ProjectPICName = g.Key.PN,
                                  ProjectCode =g.Key.PJ,

                                  In_StFu = g.Sum(o => o.In_StFu),
                                  In_PrFu = g.Sum(o => o.In_PrFu),
                                  In_Su = g.Sum(o => o.In_Su),
                                  In_DeSu = g.Sum(o => o.In_DeSu),
                                  In_ScSu = g.Sum(o => o.In_ScSu),
                                  In_PoSu = g.Sum(o => o.In_PoSu),
                                  In_MaSu = g.Sum(o => o.In_MaSu),
                                  In_B = g.Sum(o => o.In_B),
                                  In_O = g.Sum(o => o.In_O),

                                  BuPa_Eq = g.Sum(o => o.BuPa_Eq),
                                  BuPa_Ma = g.Sum(o => o.BuPa_Ma),
                                  BuPa_Co = g.Sum(o => o.BuPa_Co),
                                  BuPa_Bu = g.Sum(o => o.BuPa_Bu),
                                  BuPa_Ch = g.Sum(o => o.BuPa_Ch),
                                  BuPa_Pr = g.Sum(o => o.BuPa_Pr),
                                  BuPa_Pu = g.Sum(o => o.BuPa_Pu),
                                  BudgetPay_Amount = g.Sum(o => o.BudgetPay_Amount),
                                  BuPa_B = g.Sum(o => o.BuPa_B),
                                  BuPa_P = g.Sum(o => o.BuPa_P),

                              };

            DB.Connection.Close();

            Int32 RowIndex = 1;
            
            StringWriter sw = new StringWriter();

            string HC = " style='background-color:#D8D8D8' ";
            string H1 = " style='background-color:#D8D8D8; border:0.5pt solid black;' ";
            string H2 = " style='background-color:#D8D8D8; border-top:0.5pt solid black; border-right:0.5pt solid black; border-bottom:0.5pt solid black;' ";
            string D1 = " style='border-left:0.5pt solid black; border-right:0.5pt solid black; border-bottom:0.5pt solid black;' ";
            string D2 = " style='border-right:0.5pt solid black; border-bottom:0.5pt solid black;' ";
            
            string Type = "建设／维持";            
            if (M.Q_Type == "JJ") { Type = "建设"; }
            if (M.Q_Type == "JJ") { Type = "维持"; }
            
            string Title = "";
            switch (M.Q_Category)
            {
                case "1":
                    Title = "年度预算汇总表（科研经费）";
                    break;
                case "2":
                    Title = "年度预算汇总表（科研预算外经费）";
                    break;
                case "3":
                    Title = "年度预算汇总表（有偿服务经费）";
                    break;
                case "4":
                    Title = "年度预算汇总表（机关经费（" + Type + "））";
                    break;
            }

            string str = "";

            str += "<table width='100%' cellpadding='0' cellspacing='0' border='0' style='font-family:宋体;font-size:10pt;'>";

            #region Header

            switch (M.Q_Category)
            {
                case "1":
                    str += string.Format("<tr ><td align='center' colspan='17'{0}><B><a style='color:Red'>{1}</a>{2}</B></td></tr>", HC, M.Q_Year, Title);
                    str += string.Format("<tr><td align='left' colspan='10'{0}>编制单位：管理处财务办</td><td align='right' colspan='7'{0}>金额单位：{1}</td></tr>"
                        , HC, M.DS_Currency.Where(o => o.Value == M.Q_Currency.ToString()).First().Text);
                    str += "<tr>";
                    str += string.Format("<td rowspan='2'{0}>序号</td>", H1);
                    str += string.Format("<td rowspan='2'{0}>单位名称</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目负责人</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目代号</td>", H2);
                    str += string.Format("<td colspan='6'{0}>经费收入</td>", H2);
                    str += string.Format("<td colspan='5'{0}>经费支出</td>", H2);
                    str += string.Format("<td colspan='2'{0}>结超数</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>备注</td>", H2);
                    str += "</tr>";
                    str += "<tr>";
                    str += string.Format("<td{0}>小计</td><td{0}>标准领报</td><td{0}>项目经费</td><td{0}>家底弥补</td><td{0}>上年转入</td><td{0}>其他收入</td>", H2);
                    str += string.Format("<td{0}>小计</td><td{0}>设备费</td><td{0}>材料费</td><td{0}>协作费</td><td{0}>业务费</td>", H2);
                    str += string.Format("<td{0}>小计</td><td{0}>待预算经费</td>", H2);
                    str += "</tr>";
                    break;
                case "2":
                    str += string.Format("<tr ><td align='center' colspan='19'{0}><B><a style='color:Red'>{1}</a>{2}</B></td></tr>", HC, M.Q_Year, Title);
                    str += string.Format("<tr><td align='left' colspan='10'{0}>编制单位：管理处财务办</td><td align='right' colspan='9'{0}>金额单位：{1}</td></tr>"
                        , HC, M.DS_Currency.Where(o => o.Value == M.Q_Currency.ToString()).First().Text);
                    str += "<tr>";
                    str += string.Format("<td rowspan='2'{0}>序号</td>", H1);
                    str += string.Format("<td rowspan='2'{0}>单位名称</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目负责人</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目代号</td>", H2);
                    str += string.Format("<td colspan='6'{0}>经费收入</td>", H2);
                    str += string.Format("<td colspan='6'{0}>经费支出</td>", H2);
                    str += string.Format("<td colspan='3'{0}>结超数</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>备注</td>", H2);
                    str += "</tr>";
                    str += "<tr>";
                    str += string.Format("<td{0}>小计</td><td{0}>标准领报</td><td{0}>项目经费</td><td{0}>家底弥补</td><td{0}>上年转入</td><td{0}>其他收入</td>", H2);
                    str += string.Format("<td{0}>小计</td><td{0}>设备费</td><td{0}>材料费</td><td{0}>协作费</td><td{0}>业务费</td><td{0}>分摊费</td>", H2);
                    str += string.Format("<td{0}>小计</td><td{0}>待预算经费</td><td{0}>上缴利润</td>", H2);
                    str += "</tr>";
                    break;
                case "3":
                    str += string.Format("<tr ><td align='center' colspan='17'{0}><B><a style='color:Red'>{1}</a>{2}</B></td></tr>", HC, M.Q_Year, Title);
                    str += string.Format("<tr><td align='left' colspan='10'{0}>编制单位：管理处财务办</td><td align='right' colspan='7'{0}>金额单位：{1}</td></tr>"
                        , HC, M.DS_Currency.Where(o => o.Value == M.Q_Currency.ToString()).First().Text);
                    str += "<tr>";
                    str += string.Format("<td rowspan='2'{0}>序号</td>", H1);
                    str += string.Format("<td rowspan='2'{0}>单位名称</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目负责人</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目代号</td>", H2);
                    str += string.Format("<td colspan='6'{0}>经费收入</td>", H2);
                    str += string.Format("<td colspan='4'{0}>经费支出</td>", H2);
                    str += string.Format("<td colspan='3'{0}>结超数</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>备注</td>", H2);
                    str += "</tr>";
                    str += "<tr>";
                    str += string.Format("<td{0}>小计</td><td{0}>标准领报</td><td{0}>项目经费</td><td{0}>家底弥补</td><td{0}>上年转入</td><td{0}>其他收入</td>", H2);
                    str += string.Format("<td{0}>小计</td><td{0}>成本性费用</td><td{0}>公用费用</td><td{0}>分摊费</td>", H2);
                    str += string.Format("<td{0}>小计</td><td{0}>待预算经费</td><td{0}>上缴利润</td>", H2);
                    str += "</tr>";
                    break;
                case "4":
                    str += string.Format("<tr ><td align='center' colspan='15'{0}><B><a style='color:Red'>{1}</a>{2}</B></td></tr>", HC, M.Q_Year, Title);
                    str += string.Format("<tr><td align='left' colspan='10'{0}>编制单位：管理处财务办</td><td align='right' colspan='5'{0}>金额单位：{1}</td></tr>"
                        , HC, M.DS_Currency.Where(o => o.Value == M.Q_Currency.ToString()).First().Text);
                    str += "<tr>";
                    str += string.Format("<td rowspan='3'{0}>序号</td>", H1);
                    str += string.Format("<td rowspan='3'{0}>单位名称</td>", H2);
                    str += string.Format("<td rowspan='3'{0}>项目负责人</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目代号</td>", H2);
                    str += string.Format("<td colspan='10'{0}>经费收入</td>", H2);
                    str += string.Format("<td{0}>经费支出</td>", H2);
                    str += string.Format("<td rowspan='3'{0}>备注</td>", H2);
                    str += "</tr>";
                    str += "<tr>";
                    str += string.Format("<td rowspan='2'{0}>小计</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>标准领报</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>项目经费</td>", H2);
                    str += string.Format("<td colspan='5'{0}>家底弥补</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>上年转入</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>其他收入</td>", H2);
                    str += string.Format("<td rowspan='2'{0}>小计</td>", H2);
                    str += "</tr>";
                    str += "<tr>";
                    str += string.Format("<td{0}>小计</td><td{0}>所家底</td><td{0}>科技处家底</td><td{0}>政治部家底</td><td{0}>管理处家底</td>", H2);
                    str += "</tr>";
                    break;
            }

            #endregion

            #region Tbody

            if (ieQuery.Any())
            {
                List<vw_Budget> qtQuery = ieQuery.ToList();

                foreach (vw_Budget aQuery in qtQuery)
                {
                    str += "<tr>";

                    str += string.Format("<td{1}>{0}</td>", RowIndex, D1);
                    str += string.Format("<td{1}>{0}</td>", aQuery.DepartmentName, D2);
                    str += string.Format("<td{1}>{0}</td>", aQuery.ProjectPICName, D2);
                    str += string.Format("<td{1}>{0}</td>", aQuery.ProjectCode, D2);

                    switch (M.Q_Category)
                    {
                        case "1":
                            str += string.Format("<td{1}>{0}</td>", (aQuery.In_StFu + aQuery.In_PrFu + aQuery.In_Su + aQuery.In_B + aQuery.In_O) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_StFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_PrFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_Su / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_O / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", (aQuery.BuPa_Eq + aQuery.BuPa_Ma + aQuery.BuPa_Co + aQuery.BuPa_Bu) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Eq / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Ma / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Co / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Bu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_B / M.Q_Currency, D2);
                            break;
                        case "2":
                            str += string.Format("<td{1}>{0}</td>", (aQuery.In_StFu + aQuery.In_PrFu + aQuery.In_Su + aQuery.In_B + aQuery.In_O) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_StFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_PrFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_Su / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_O / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", (aQuery.BuPa_Eq + aQuery.BuPa_Ma + aQuery.BuPa_Co + aQuery.BuPa_Bu + aQuery.BuPa_Ch) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Eq / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Ma / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Co / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Bu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Ch / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", (aQuery.BuPa_B + aQuery.BuPa_P) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_P / M.Q_Currency, D2);
                            break;
                        case "3":
                            str += string.Format("<td{1}>{0}</td>", (aQuery.In_StFu + aQuery.In_PrFu + aQuery.In_Su + aQuery.In_B + aQuery.In_O) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_StFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_PrFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_Su / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_O / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", (aQuery.BuPa_Pr + aQuery.BuPa_Pu + aQuery.BuPa_Ch) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Pr / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Pu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_Ch / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", (aQuery.BuPa_B + aQuery.BuPa_P) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BuPa_P / M.Q_Currency, D2);
                            break;
                        case "4":
                            str += string.Format("<td{1}>{0}</td>", (aQuery.In_StFu + aQuery.In_PrFu + aQuery.In_DeSu + aQuery.In_ScSu + aQuery.In_PoSu + aQuery.In_MaSu + aQuery.In_B + aQuery.In_O) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_StFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_PrFu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", (aQuery.In_DeSu + aQuery.In_ScSu + aQuery.In_PoSu + aQuery.In_MaSu) / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_DeSu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_ScSu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_PoSu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_MaSu / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_B / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.In_O / M.Q_Currency, D2);
                            str += string.Format("<td{1}>{0}</td>", aQuery.BudgetPay_Amount / M.Q_Currency, D2);
                            break;
                    }

                    str += string.Format("<td{1}>{0}</td>", " ", D2);
                    str += "</tr>";
                    RowIndex++;
                }
            }

            str += "</table>";

            sw.Write(str);
            sw.Close();

            #endregion

            Response.ClearHeaders();
            Response.ClearContent();
            Response.Clear();

            Response.Charset = "UTF-8";
            //HttpContext.Current.Response.Charset = "GB2312";

            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(M.Q_Year + Title) + ".xls");
            Response.ContentType = "application/ms-excel";

            Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");
            Response.Write(sw);
            Response.Flush();
            Response.End();
            Response.Close();

            return View(M);
        }

        #endregion

        private Boolean Budget_Upload_Check(String fileName, String PC, ref List<TemplateSheetItem> lsSheet, L_ActionType Action, ref String Error)
        {
            Excel._Application app = null;
            Excel.Workbook book = null;
            Excel.Worksheet ws = null;
            ExcelVar Var = null;

            try
            {
                Dictionary<String, String> lsPC = new Dictionary<String, String>();
                Dictionary<String, String> lsPT = new Dictionary<String, String>();
                Dictionary<String, String> lsBS = new Dictionary<String, String>();
                Dictionary<String, String> lsYN = new Dictionary<String, String>();
                Dictionary<String, String> lsD = new Dictionary<String, String>();
                Dictionary<String, String> lsAP = new Dictionary<String, String>();
                Dictionary<String, String> lsU = new Dictionary<String, String>();
                Dictionary<String, String> lsY = new Dictionary<String, String>();

                ExcelSheetItem.GetList(ref lsPC, ref lsPT, ref lsBS, ref lsYN, ref lsD, ref lsAP, ref lsU, ref lsY);

                app = new Excel.ApplicationClass();

                try
                {
                    book = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                    Var = new ExcelVar((Excel.Worksheet)book.Worksheets["Var"], app);
                }
                catch (Exception)
                {
                    throw new Exception(Resources.Message.Alter_FileReadFail);
                }

                String ProjectCategory = Var.PjCategoryID;
                String ProjectType = Var.PjType;

                if (PC == "1" && !new String[] { "1", "2", "3" }.Contains(ProjectCategory) && (Action == L_ActionType.Upload))
                {
                    throw new Exception("不是有效的一般预算");
                }

                if (PC == "4" && (ProjectCategory != "4") && (Action == L_ActionType.Upload))
                {
                    throw new Exception("不是有效的机关费预算");
                }





                for (int i = 1; i <= book.Worksheets.Count; i++)
                {
                    ws = (Excel.Worksheet)book.Worksheets.get_Item(i);

                    if (ws.Name.ToLower() == "welcome" || ws.Name.ToLower() == "var")
                        continue;

                    TemplateSheetItem oSheet = new TemplateSheetItem();

                    if (new String[] { "1", "2", "3" }.Contains(ProjectCategory))
                    {
                        #region 一般预算处理

                        oSheet.BugetInfo.Add("类别", new ExcelSheetCellItem() { Value = ProjectCategory });
                        oSheet.BugetInfo.Add("类型", new ExcelSheetCellItem() { Value = String.Empty });
                        oSheet.BugetInfo.Add("年度", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B1").Text), IsRequired = true, IsList = true, InList = lsY });
                        oSheet.BugetInfo.Add("编制单位", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B2").Text), IsRequired = true, MaxLength = 20, IsList = true, InList = lsD });
                        oSheet.BugetInfo.Add("项目名称", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B3").Text), IsRequired = true, MaxLength = 50 });
                        oSheet.BugetInfo.Add("项目代号", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B4").Text), IsRequired = true, MaxLength = 10 });
                        oSheet.BugetInfo.Add("年度进度", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D4").Text), IsRequired = true });
                        oSheet.BugetInfo.Add("经费来源", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G4").Text), IsRequired = true, MaxLength = 20 });
                        oSheet.BugetInfo.Add("项目周期-起", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B5").Text), IsRequired = true, IsDate = true });
                        oSheet.BugetInfo.Add("项目周期-止", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D5").Text), IsRequired = true, IsDate = true });
                        oSheet.BugetInfo.Add("项目负责人", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F5").Text), IsRequired = true, IsList = true, InList = lsU });
                        if (ProjectCategory == "1" || ProjectCategory == "2")
                            oSheet.BugetInfo.Add("是否为软课题", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H5").Text), IsRequired = true, IsList = true, InList = lsYN });
                        oSheet.BugetInfo.Add("是否垫支", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A7").Text), IsRequired = true, IsList = true, InList = lsYN });
                        oSheet.BugetInfo.Add("拟到经费总额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B7").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("预计经费到位时间", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C7").Text), IsDate = true });
                        oSheet.BugetInfo.Add("预计归垫时间", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D7").Text), IsDate = true });
                        oSheet.BugetInfo.Add("归垫责任人", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E7").Text), IsList = true, InList = lsU });
                        oSheet.BugetInfo.Add("垫支金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F7").Text), IsDecimal = true });
                        if (lsYN[oSheet.BugetInfo["是否垫支"].Text].ToUpper() == "TRUE")
                        {
                            oSheet.BugetInfo["拟到经费总额"].IsRequired = true;
                            oSheet.BugetInfo["预计经费到位时间"].IsRequired = true;
                            oSheet.BugetInfo["预计归垫时间"].IsRequired = true;
                            oSheet.BugetInfo["归垫责任人"].IsRequired = true;
                            oSheet.BugetInfo["垫支金额"].IsRequired = true;
                        }

                        oSheet.BugetInfo.Add("垫支转入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D8").Text), IsDecimal = true });

                        oSheet.BugetInfo.Add("垫支备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G7").Text), MaxLength = 50 });
                        oSheet.BugetInfo.Add("预算金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A10").Text) });
                        oSheet.BugetInfo.Add("标准领报", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B10").Text), IsRequired = true, IsDecimal = true });
                        oSheet.BugetInfo.Add("项目经费", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C10").Text), IsRequired = true, IsDecimal = true });
                        oSheet.BugetInfo.Add("家底弥补", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D10").Text), IsRequired = true, IsDecimal = true });
                        oSheet.BugetInfo.Add("其他收入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E10").Text), IsRequired = true, IsDecimal = true });
                        oSheet.BugetInfo.Add("上年转入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F10").Text), IsRequired = true, IsDecimal = true });
                        oSheet.BugetInfo.Add("预算备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G10").Text), MaxLength = 50 });

                        if (ProjectCategory == "1")
                        {
                            oSheet.BugetInfo.Add("当年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[设备费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[材料费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[协作费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[业务费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[待预算经费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G12").Text), MaxLength = 50 });

                            oSheet.BugetInfo.Add("历年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B13").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[设备费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[材料费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[协作费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[业务费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D13").Text), MaxLength = 50 });

                            oSheet.BugetInfo.Add("当年支出实际", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F13").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[设备费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[材料费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[协作费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[业务费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H15").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H13").Text), MaxLength = 50 });
                        }

                        if (ProjectCategory == "2")
                        {
                            oSheet.BugetInfo.Add("当年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[设备费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[材料费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[协作费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[业务费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[分摊费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[待预算经费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[上缴利润]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H12").Text), IsDecimal = true });

                            oSheet.BugetInfo.Add("历年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[设备费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[材料费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[协作费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[业务费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[分摊费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G14").Text), MaxLength = 50 });

                            oSheet.BugetInfo.Add("当年支出实际", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A16").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[设备费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B16").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[材料费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C16").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[协作费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D16").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[业务费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E16").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[分摊费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F16").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G16").Text), MaxLength = 50 });
                        }

                        if (ProjectCategory == "3")
                        {
                            oSheet.BugetInfo.Add("当年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[成本性]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[公用费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[分摊费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[待预算经费]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出[上缴利润]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F12").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G12").Text), MaxLength = 50 });

                            oSheet.BugetInfo.Add("历年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[成本性]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[公用费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("历年支出[分摊费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D14").Text), IsDecimal = true });

                            oSheet.BugetInfo.Add("当年支出实际", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[成本性]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[公用费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G14").Text), IsDecimal = true });
                            oSheet.BugetInfo.Add("当年支出实际[分摊费用]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H14").Text), IsDecimal = true });
                        }

                        oSheet.BugetInfo.Add("报警信息", new ExcelSheetCellItem());

                        //预算科研项目/预算外科研项目 需要提醒“软课题相关的业务费超40%，超20%”
                        if (ProjectCategory == "1" || ProjectCategory == "2")
                        {
                            Int32 BussniessRow = Convert.ToInt32(app.WorksheetFunction.Match(400, ws.get_Range("N:N"), 0));
                            if (ws.get_Range("I" + BussniessRow.ToString()).FormulaR1C1.ToString() != string.Empty)
                            {
                                if (oSheet.BugetInfo["报警信息"].Text != String.Empty) { oSheet.BugetInfo["报警信息"].Text += "<br/>"; }
                                oSheet.BugetInfo["报警信息"].Text += ws.get_Range("I" + BussniessRow.ToString()).FormulaR1C1.ToString();
                            }
                        }

                        //预算外科研项目/有偿服务项目 需要提醒“上缴是否小于总收入5%”
                        if (ProjectCategory == "2" || ProjectCategory == "3")
                        {
                            Int32 ProfitRow = Convert.ToInt32(app.WorksheetFunction.Match("Profit", ws.get_Range("N:N"), 0));
                            if (ws.get_Range("I" + ProfitRow.ToString()).FormulaR1C1.ToString() != string.Empty)
                            {
                                if (oSheet.BugetInfo["报警信息"].Text != String.Empty) { oSheet.BugetInfo["报警信息"].Text += "<br/>"; }
                                oSheet.BugetInfo["报警信息"].Text += ws.get_Range("I" + ProfitRow.ToString()).FormulaR1C1.ToString();
                            }
                        }

                        oSheet.BugetInfo.Add("检查结果", new ExcelSheetCellItem());
                        oSheet.BugetInfo.Add("处理方案", new ExcelSheetCellItem());

                        int r_min = 0;

                        if (ProjectCategory == "1") { r_min = 18; }
                        if (ProjectCategory == "2") { r_min = 19; }
                        if (ProjectCategory == "3") { r_min = 17; }

                        for (int r = r_min; r < ws.UsedRange.Rows.Count; r++)
                        {
                            String Flag = Convert.ToString(ws.get_Range("N" + r.ToString()).Value2);

                            if (Flag.ToUpper() == "9999")
                            {
                                if (ProjectCategory == "2" || ProjectCategory == "3")
                                    oSheet.BugetInfo.Add("待预算经费", new ExcelSheetCellItem()
                                    {
                                        Text = Convert.ToString(ws.get_Range("F" + (r + 1).ToString()).Text),
                                        IsDecimal = true
                                    });

                                break;
                            }

                            Dictionary<String, ExcelSheetCellItem> dicItem = new Dictionary<string, ExcelSheetCellItem>();

                            dicItem.Add("序号", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A" + r.ToString()).Text) });
                            dicItem.Add("支出品名", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B" + r.ToString()).Text) });
                            if (!Convert.ToBoolean(ws.get_Range("B" + r.ToString()).Locked))
                                dicItem["支出品名"].IsRequired = true;
                            dicItem.Add("数量", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D" + r.ToString()).Text), IsNum = true });
                            if (!Convert.ToBoolean(ws.get_Range("D" + r.ToString()).Locked))
                                dicItem["数量"].IsRequired = true;
                            dicItem.Add("单价", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E" + r.ToString()).Text), IsDecimal = true });
                            if (!Convert.ToBoolean(ws.get_Range("E" + r.ToString()).Locked))
                                dicItem["单价"].IsRequired = true;
                            dicItem.Add("金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F" + r.ToString()).Text), IsDecimal = true });
                            if (!Convert.ToBoolean(ws.get_Range("F" + r.ToString()).Locked))
                                dicItem["金额"].IsRequired = true;
                            dicItem.Add("Lv", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("L" + r.ToString()).Value2) });
                            dicItem.Add("PName", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("M" + r.ToString()).Value2) });
                            dicItem.Add("ID", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("N" + r.ToString()).Value2) });
                            dicItem.Add("备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H" + r.ToString()).Text), MaxLength = 50 });
                            if (
                                (ProjectCategory == "1" && new String[] { "207", "307", "308", "402" }.Contains(dicItem["ID"].Text) && !String.IsNullOrEmpty(dicItem["金额"].Text)) ||
                                (ProjectCategory == "2" && new String[] { "206", "307", "308", "402" }.Contains(dicItem["ID"].Text) && !String.IsNullOrEmpty(dicItem["金额"].Text)) ||
                                (ProjectCategory == "3" && new String[] { "113", "115", "202" }.Contains(dicItem["ID"].Text) && !String.IsNullOrEmpty(dicItem["金额"].Text))
                                )
                            {
                                Decimal dTemp = 0;
                                Decimal.TryParse(dicItem["金额"].Text, out dTemp);

                                if (dTemp > 0)
                                {
                                    dicItem["备注"].IsRequired = true;
                                }
                            }
                            dicItem.Add("Row", new ExcelSheetCellItem() { Text = r.ToString() });

                            oSheet.ItemInfo.Add(dicItem);
                        }

                        if (Convert.ToString(ws.get_Range("N3").Value2).ToUpper() == "FALSE")
                        {
                            oSheet.BugetInfo["检查结果"].Text += "模板必要校验未通过<br/>";
                            oSheet.IsOK = false;
                        }

                        lsSheet.Add(oSheet);

                        #endregion
                    }

                    if (ProjectCategory == "4")
                    {
                        #region 机关经费处理

                        if (ws.Visible == Excel.XlSheetVisibility.xlSheetVeryHidden)
                            continue;

                        String ProjectCode = Var.GetSheetVar(ws.CodeName, "PJCode");

                        oSheet.BugetInfo.Add("类别", new ExcelSheetCellItem() { Value = ProjectCategory });
                        oSheet.BugetInfo.Add("年度", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B1").Text), IsRequired = true, IsList = true, InList = lsY });
                        oSheet.BugetInfo.Add("编制单位", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B2").Text), IsRequired = true, MaxLength = 20, IsList = true, InList = lsD });
                        oSheet.BugetInfo.Add("项目名称", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B3").Text), IsRequired = true, MaxLength = 50 });
                        oSheet.BugetInfo.Add("项目代号", new ExcelSheetCellItem() { Text = ProjectCode });
                        oSheet.BugetInfo.Add("类型", new ExcelSheetCellItem() { Value = ProjectType });
                        oSheet.BugetInfo.Add("项目负责人", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("I3").Text), IsRequired = true, IsList = true, InList = lsU });
                        oSheet.BugetInfo.Add("是否垫支", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A5").Text), IsRequired = true, IsList = true, InList = lsYN });
                        oSheet.BugetInfo.Add("拟到经费总额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B5").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("预计经费到位时间", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D5").Text), IsDate = true });
                        oSheet.BugetInfo.Add("预计归垫时间", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F5").Text), IsDate = true });
                        oSheet.BugetInfo.Add("归垫责任人", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H5").Text), IsList = true, InList = lsU });
                        oSheet.BugetInfo.Add("垫支金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("J5").Text), IsDecimal = true });
                        if (lsYN[oSheet.BugetInfo["是否垫支"].Text].ToUpper() == "TRUE")
                        {
                            oSheet.BugetInfo["拟到经费总额"].IsRequired = true;
                            oSheet.BugetInfo["预计经费到位时间"].IsRequired = true;
                            oSheet.BugetInfo["预计归垫时间"].IsRequired = true;
                            oSheet.BugetInfo["归垫责任人"].IsRequired = true;
                            oSheet.BugetInfo["垫支金额"].IsRequired = true;
                        }
                        oSheet.BugetInfo.Add("垫支备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("J6").Text), MaxLength = 50 });

                        oSheet.BugetInfo.Add("垫支转入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D6").Text), IsDecimal = true });

                        oSheet.BugetInfo.Add("预算金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A9").Text) });
                        oSheet.BugetInfo.Add("标准领报", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("项目经费", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("家底弥补[所家底]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("D9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("家底弥补[科技处家底]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("家底弥补[政治部家底]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("家底弥补[管理处家底]", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("其他收入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("上年转入", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("I9").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("预算备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("J9").Text), MaxLength = 50 });

                        oSheet.BugetInfo.Add("当年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A11").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("历年支出", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("C11").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("当年支出实际", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E11").Text), IsDecimal = true });
                        oSheet.BugetInfo.Add("当年支出备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G11").Text) });

                        oSheet.BugetInfo.Add("检查结果", new ExcelSheetCellItem());
                        oSheet.BugetInfo.Add("处理方案", new ExcelSheetCellItem());

                        int No = 1;

                        for (int r = 14; r < ws.UsedRange.Rows.Count; r++)
                        {
                            Int32 intID = 0;

                            Int32.TryParse(Convert.ToString(ws.get_Range("P" + r.ToString()).Value2), out intID);

                            if (intID >= 9999) { break; }

                            Dictionary<String, ExcelSheetCellItem> dicItem = new Dictionary<string, ExcelSheetCellItem>();

                            dicItem.Add("序号", new ExcelSheetCellItem() { Text = No.ToString() }); No++;
                            //dicItem.Add("序号", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("A" + r.ToString()).Text) });
                            dicItem.Add("预算开支项目", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("B" + r.ToString()).Text) });
                            dicItem.Add("单位", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("E" + r.ToString()).Text) });

                            dicItem.Add("单价", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("F" + r.ToString()).Text), IsDecimal = true });
                            dicItem.Add("数量", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("G" + r.ToString()).Text), IsNum = true });
                            dicItem.Add("预算开支金额", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("H" + r.ToString()).Text), IsDecimal = true });
                            dicItem.Add("备注", new ExcelSheetCellItem() { Text = Convert.ToString(ws.get_Range("J" + r.ToString()).Text), MaxLength = 50 });
                            dicItem.Add("Row", new ExcelSheetCellItem() { Text = r.ToString() });

                            oSheet.ItemInfo.Add(dicItem);
                        }

                        if (Convert.ToString(ws.get_Range("P3").Value2).ToUpper() == "FALSE")
                        {
                            oSheet.BugetInfo["检查结果"].Text += "模板必要校验未通过<br/>";
                            oSheet.IsOK = false;
                        }

                        lsSheet.Add(oSheet);

                        #endregion
                    }
                }

                TemplateSheetItem.Check(lsSheet, CurrentUser);

                //TemplateSheetItem.CheckLogic(lsSheet);

                TemplateSheetItem.CheckDB(lsSheet, CurrentUser, Action);

                return true;
            }
            catch (Exception ex)
            {
                //LOG
                //throw ex;
                Error = ex.Message;

                return false;
            }
            finally
            {
                if (book != null)
                    book.Close(false, Type.Missing, Type.Missing);

                if (app != null)
                    app.Quit();

                ws = null;
                book = null;
                app = null;

                GC.Collect();

                System.IO.File.Delete(fileName);
            }
        }

    }
}
